""" 数据库操作类 """
from plugin.logger import MyLogger
from dbutils.pooled_db import PooledDB
from plugin.exception import MyException
import sqlite3

logger = MyLogger.logger


class DB:
    """ 数据库操作类，父类 """
    __pool: dict = {}  # link: pool 每个数据库连接参数有自己的线程池

    def __init__(self, module, mincached=5, maxcached=10, maxconnections=100, blocking=True, maxusage=100, **conninfo) -> None:
        """
        @param:
            module: 数据库连接包，如pymysql
            host: 数据库ip
            port: 数据库端口
            db: 库名
            user: 用户名
            passwd: 密码
            charset: 字符编码
            mincached: 连接池中空闲连接的初始数量
            maxcached: 连接池中空闲连接的最大数量
            maxconnections: 创建连接池的最大数量
            blocking: 超过最大连接数量时候的表现，为True等待连接数量下降，为false直接报错处理
            maxusage: 单个连接的最大重复使用次数
        """
        link = ''  # 连接参数拼接的字符串，用作线程池的key
        for key, value in conninfo.items():  # dict不能作为key，只好把他们连接起来
            assert value, '数据库连接初始化失败，缺少必要字段{}'.format(key)
            link += str(value)
        if not self.__class__.__pool.__contains__(link):
            try:
                pool = PooledDB(module,
                                mincached,
                                maxcached,
                                maxshared=10,
                                maxconnections=maxconnections,
                                blocking=blocking,
                                maxusage=maxusage,
                                setsession=None,
                                reset=True,
                                **conninfo)
                # cursorclass=module.cursors.DictCursor)

                self.__class__.__pool[link] = pool
            except Exception as e:
                logger.error(MyException.ERROR_DATABASE_CONN)
                logger.error(e)
        self._link = link
        self._conn = None
        self._cursor = None
        self._module = str(module.__name__)
        self.__get_conn()

    def __get_conn(self):
        try:
            self._conn = self.__pool[self._link].connection()
            self._cursor = self._conn.cursor()
        except Exception as e:
            logger.error(MyException.ERROR_DATABASE_CONN)
            logger.error(e)

    def close(self):
        try:
            self._cursor.close()
            self._conn.close()
        except Exception as e:
            logger.error(e)

    def execute(self, sql, args=None):
        try:
            effect_rows = self.__execute(sql, param=args)
            self._conn.commit()
        except Exception as e:
            self._conn.rollback()
            logger.error(e)
            return None
            # raise e
        return effect_rows

    def executemany(self, sql, args=None):
        try:
            effect_rows = self._cursor.executemany(sql, args)
            self._conn.commit()
        except Exception as e:
            logger.error(e)
            raise e
        return effect_rows

    def __execute(self, sql, param=()):
        try:
            effect_rows = self._cursor.execute(sql, param)
            if self._module == 'pymssql':
                effect_rows = self._cursor.rowcount
            return effect_rows or 0
        except Exception as e:
            logger.error(MyException.ERROR_DATABASE_EXEC)
            logger.error(sql)
            logger.error(e)
            return None

    def select(self, sql, param=()):
        """
        查询多个结果
        :param sql: qsl语句
        :param param: sql参数
        :return: 结果数量和查询结果集
        """
        try:
            count = self.__execute(sql, param)
            result = self._cursor.fetchall()
            return result
        except Exception as e:
            # logger.error(MyException.ERROR_DATABASE_WORK)
            # logger.error(e)
            raise MyException(MyException.ERROR_DATABASE_WORK)
            return None

    def execute_without_commit(self, sql, param=()):
        count = self.__execute(sql, param)
        return count

    def begin(self):
        """开启事务"""
        self._conn.autocommit(0)

    def end(self, option='commit'):
        """结束事务"""
        if option == 'commit':
            self._conn.autocommit()
        else:
            self._conn.rollback()


class MysqlClient(DB):
    """ mysql操作类 """

    def __init__(self,
                 host=None,
                 port=None,
                 database=None,
                 user=None,
                 password=None,
                 charset='utf8',
                 mincached=5,
                 maxcached=10,
                 maxconnections=100,
                 blocking=True,
                 maxusage=100) -> None:
        try:
            import pymysql
        except Exception as e:
            logger.error(e)
            return
        conn_info = {
            'host': host,
            'port': port,
            'db': database,
            'user': user,
            'passwd': password,
            'charset': charset,
            'cursorclass': pymysql.cursors.DictCursor
        }
        super().__init__(
            pymysql,
            #  charset=charset,
            mincached=mincached,
            maxcached=maxcached,
            maxconnections=maxconnections,
            blocking=blocking,
            maxusage=maxusage,
            **conn_info)

    def select(self, sql, param=()):
        """
        查询
        :param sql: qsl语句
        :param param: sql参数
        :return: list(dict),exp: [{'id': 1, 'name': 'test'}, {'id': 2, 'name': 'test2'}]
        """
        return super().select(sql, param)

    # def select_to_dict(self, sql, param=()) -> list:
    #     """ 将查询的结果和title组成list(dict) """
    #     result = []
    #     datas = super().select(sql, param)
    #     if datas is not None:
    #         title = [item[0] for item in self._cursor.description]
    #         for data in datas:
    #             result.append(dict(zip(title, data)))
    #     return result


class MSsqlClient(DB):
    """ sqlserver操作类 """

    def __init__(self,
                 host=None,
                 port=None,
                 database=None,
                 user=None,
                 password=None,
                 charset='utf8',
                 mincached=5,
                 maxcached=10,
                 maxconnections=100,
                 blocking=True,
                 maxusage=100,
                 rw='w') -> None:
        try:
            import pymssql
        except Exception as e:
            logger.error(MyException.ERROR_DATABASE_WORK)
            logger.error(e)
            raise MyException(MyException.ERROR_DATABASE_WORK)
        charset = 'utf8' if rw == 'w' else 'cp936'
        conn_info = {'host': host, 'port': port, 'database': database, 'user': user, 'password': password, 'charset': charset}
        super().__init__(pymssql,
                         mincached=mincached,
                         maxcached=maxcached,
                         maxconnections=maxconnections,
                         blocking=blocking,
                         maxusage=maxusage,
                         **conn_info)

    def select_to_dict(self, sql, param=()) -> list[dict]:
        """
        将查询的结果和title组成list(dict) 
        @param:
            @sql: sql语句
            @param: sql参数
        @return:
            @result: type->list[dict] ep: [{'id': 1, 'name': 'test'}, {'id': 2, 'name': 'test2'}]
        """
        result = []
        # datas = super().select(sql, param)
        datas = self.select(sql, param)
        if datas is not None:
            title = [item[0] for item in self._cursor.description]
            for data in datas:
                result.append(dict(zip(title, data)))
        return result

    def insert_objects(self,
                       objects: list,
                       tablename=None,
                       columns: list = None,
                       create_table=False,
                       increment_id: bool = False,
                       create_time: bool = False,
                       pack_size: int = 300):
        """
        将对象列表插入数据库，需要提供数据和表名，将自动切割数据并拼接语句插入
        @param:
            @datas: 对象类型的数据
            @tablename: 数据库名称
            @columns: 提供的列明，没有提供的话，默认时datas的字段名
            @create_table: 是否创建表
            @increment_id: 是否创建自增id
            @create_time: 是否创建创建时间
            @pack_size: 每次插入的数据量
        @return:
            @result: type->bool
        """
        if not objects or len(objects) == 0:
            return True
        columns = list(objects[0].__dict__.keys()) if not columns else columns  # 获取列名
        tablename = objects[0].__class__.__name__ if not tablename else tablename  # 获取表名
        if create_table:
            self.create_table(tablename, columns, increment_id, create_time)

        sql = "insert into {} ({}) values ({})".format(tablename, ','.join(columns), ','.join(['?'] * len(columns)))
        self.insertmany(sql, [tuple(data.__dict__.values()) for data in objects], pack_size)
        return True

    def insertmany(self, sql, params=[()], each=300):
        """
        批量插入
        @param:
            @sql: sql语句 ep: insert into table (id,name) values (?,?)
            @params: sql参数 ep: [(1,'test'),(2,'test2')]
            @each: 每次插入的条数，默认300。 由于sqlserver的限制，每次插入的条数不能超过1000
        """
        sqls = sql.split('values')
        insert_sql = sqls[0] + 'values '
        value_sql = sqls[1].replace("?", "%s").replace("%s", "'%s'")
        for cut_params in [params[i:i + each] for i in range(0, len(params), each)]:  # 拆分数据
            values_sql = ','.join([(value_sql % param) for param in cut_params])
            self.execute(insert_sql + values_sql)
        return True

    def create_table(self, tablename=None, columns: list = None, id_increment: bool = False, create_time: bool = False):
        """
        创建表
        @param:
            @tablename: 表名
            @columns: 列名
            @id_increment: 是否创建自增id
            @create_time: 是否创建创建时间
            @update_time: 是否创建更新时间
        @return:
        """
        create_time_sql = "create_time datetime, " if create_time else ''
        # update_time_sql = "update_time datetime " if update_time else ''
        increment_id_sql = "id int primary key identity(1,1)," if id_increment else ''
        create_time_default_sql = "alter table {} add default (getdate()) for create_time".format(tablename) if create_time else ''
        # update_time_default_sql = "alter table {} add default (getdate()) for update_time".format(tablename) if update_time else ''

        crate_sql = """
            if not exists (select * from sysobjects where name='{0}' and xtype='U')
            begin
                create table {0}(
                    {2}
                    {3}
                    {1}
                )
                {4}
            end
            """.format(tablename, ' varchar(1024),'.join(columns) + ' varchar(1024)', increment_id_sql, create_time_sql, create_time_default_sql)
        self.execute(crate_sql)

    def __insert_objects(self, objects: list[object], tablename=None, columns: list = None, crete_table=True, increment_id: bool = False):
        """
        ****** 不要使用，效率太低，代码仅作示例 ******
        将对象列表插入数据库，需要提供数据和表名，将自动切割数据并拼接语句插入
        @param:
            @datas: 对象类型的数据
            @tablename: 数据库名称,默认类名
            @columns: 提供的列明，没有提供的话，默认时datas的字段名.
            @crete_table: default->True  是否创建表 
            @increment_id: default->False  是否自增id 

        @return:
        """
        if not objects or len(objects) == 0:
            return True
        tablename = objects[0].__class__.__name__ if not tablename else tablename
        columns = list(objects[0].__dict__.keys()) if not columns else columns  # 获取列名
        if crete_table:
            self.create_table(tablename, columns, increment_id)
        self.executemany("insert into {} values ({})".format(tablename, ('%s,' * len(columns))[:-1]),
                         [tuple(data.__dict__.values()) for data in objects])
        return True


class RedisClient(DB):
    """ redis操作类,get获取，set插入 """

    __pool: dict = {}  # link: pool 每个数据库连接参数有自己的线程池

    def __init__(self, host=None, port=None, password=None, db=0, maxconnections=100):
        try:
            import redis
        except Exception as e:
            logger.error(MyException.ERROR_DATABASE_WORK)
            logger.error(e)
            return
        link = host + port + str(db)  # 连接参数拼接的字符串，用作线程池的key
        if not self.__class__.__pool.__contains__(link):
            try:
                pool = redis.ConnectionPool(host=host, port=port, db=db, password=password, max_connections=maxconnections, decode_responses=False)
                self.__class__.__pool[link] = pool
            except Exception as e:
                logger.error(MyException.ERROR_DATABASE_CONN)
                logger.error(e)
        self._link = link
        self._conn = redis.Redis(connection_pool=self.__pool[link])

    def __execute(self, datas: dict, mode: int):
        try:
            if mode == 0:  # 更新&&插入
                self._conn.mset(datas)
            else:
                for key, value in datas:
                    if mode == 1:  # 插入，如果已存在，不更新
                        self._conn.set(key, value, nx=True)
                    elif mode == 2:  # 更新，如果不存在，不处理
                        self._conn.set(key, value, xx=True)
            return True
        except Exception as e:
            logger.error('redis更新数据出错')
            logger.error(e)
            return False

    def update(self, datas: dict):
        """ 仅更新已有的 """
        return self.__execute(datas, 2)

    def add(self, datas: dict):
        """ 仅新增没有的 """
        return self.__execute(datas, 1)

    def set(self, datas: dict):
        """ 没有的进行新增，已有的进行更新 """
        return self.__execute(datas, 0)

    def get(self, keys: list):
        """
        查询多个结果
        """
        result: list = self._conn.mget(keys)
        return result

    def get_to_dict(self, keys: list):
        """ 将查询结果和key组合成字典 """
        result = self._conn.mget(keys)
        return dict(zip(keys, result))


class Sqlite():
    """ sqlite操作类 """
    from config import get_current_path
    database = get_current_path('data.db')
    __conn = None

    def __init__(self, dbname=None) -> None:
        """ 默认读写项目文件下面的data.db """
        dbname = dbname or Sqlite.database
        if not self.__class__.__conn:
            self.__class__.__conn = sqlite3.connect(dbname)

    def __select(self, sql, param=()):
        """
         内部查询 
            @param:
                @sql: 查询语句
                @param: 查询参数
            @return:
                @result: type->list 查询结果 ep:[(1,'test'),(2,'test2')]
                @column: type->tuple 查询结果的列名 ep:(('key',none),('value',none))
        """
        cursor = self.__conn.cursor()
        cursor.execute(sql, param)
        result = cursor.fetchall()
        column = cursor.description
        cursor.close()
        return result, column

    def select(self, sql, param=()) -> list:
        """
         查询 
            @param:
                @sql: 查询语句
                @param: 查询参数
            @return:
                @result: type->list 查询结果 ep:[(1,'test'),(2,'test2')]
        """

        return self.__select(sql, param)[0]

    def execute(self, sql, param=()):
        """
         执行 
            @param:
                @sql: 查询语句
                @param: 查询参数
        """
        cursor = self.__conn.cursor()
        cursor.execute(sql, param)
        self.__conn.commit()
        cursor.close()

    def executemany(self, sql, param=()):
        """
         批量执行 
            @param:
                @sql: 查询语句
                @param: type->list(tuple) 查询参数
        """
        cursor = self.__conn.cursor()
        cursor.executemany(sql, param)
        self.__conn.commit()
        cursor.close()

    def select_to_dict(self, sql, param=()):
        """
         查询结果转换成字典 
            @param:
                @sql: 查询语句
                @param: 查询参数
            @return:
                @result: type->list[dict]  查询结果 ep:[{'id':1,'name':'test'},{'id':2,'name':'test2'}]
        """
        result, column = self.__select(sql, param)
        return [dict(zip([c[0] for c in column], r)) for r in result]

    def insert_object_list(self, objects: list, tablename=None, columns: list = None):
        """
        将对象列表插入数据库，需要提供数据和表名，将自动切割数据并拼接语句插入
        @param:
            @datas: 对象类型的数据
            @tablename: 数据库名称
            @columns: 提供的列名，没有提供的话，默认时datas的字段名
        """
        if not objects or len(objects) == 0:
            return True
        tablename = tablename or objects[0].__class__.__name__
        columns = list(objects[0].__dict__.keys()) if not columns else columns  # 获取列名
        ins_sql = "insert into {} ({}) values ".format(tablename, ','.join(columns))  # 拼接操作语句
        value_sql = "(" + ("'{}'," * len(columns))[:-1] + ")"  # 拼接单个值语句
        for cut_datas in [objects[i:i + 300] for i in range(0, len(objects), 300)]:
            sql = ins_sql + ','.join([value_sql.format(*data.__dict__.values()) for data in cut_datas])  # 拼接成插入300条的长语句
            self.execute(sql)
        return True

    def update_object_list(self, objects: list, tablename=None, columns: list = None, where_columns: list = None):
        """
        将对象列表更新数据库，需要提供数据和表名，将自动切割数据并拼接语句插入
        @param:
            @datas: 对象类型的数据
            @tablename: 数据库名称,默认为对象类型的名称
            @columns: 提供的列名，没有提供的话，默认时datas的字段名
            @where_columns: 条件列名，没有提供的话，默认为id
        """
        if not objects or len(objects) == 0:
            return True
        tablename = tablename or objects[0].__class__.__name__
        columns = columns or list(objects[0].__dict__.keys())  # 获取列名
        where_columns = where_columns or ['id']  # 获取条件列名
        upd_columns = [col for col in columns if col not in where_columns]  # 去除条件列名

        upd_set_sql = ','.join(["{} = '{{{}}}'".format(column, column) for column in upd_columns])
        upd_where_sql = ' and '.join(["{} = '{{{}}}'".format(column, column) for column in where_columns])
        #  👇 update tablename set column1={column1},column2={column2} where id={id}
        upd_sql = "update {} set {} where {}".format(tablename, upd_set_sql, upd_where_sql)  # 拼接操作语句
        for cut_datas in [objects[i:i + 300] for i in range(0, len(objects), 300)]:
            sql = ';'.join([upd_sql.format(**data.__dict__) for data in cut_datas])  # 拼接成插入300条的长语句
            self.execute(sql)
        return True


class SqliteIndep(Sqlite):

    from config import get_current_path
    database = get_current_path('data.db')

    def __init__(self, dbname=None) -> None:
        """ 默认读写项目文件下面的data.db """
        dbname = dbname or Sqlite.database
        self.__conn = sqlite3.connect(dbname)

    def execute(self, sql, param=()):
        """
         执行 
            @param:
                @sql: 查询语句
                @param: 查询参数
        """
        cursor = self.__conn.cursor()
        cursor.execute(sql, param)
        self.__conn.commit()
        cursor.close()


class Influx():
    """ influxdb操作类 """
    __conn_pool = {}
    _link = None
    _conn = None

    def __init__(self, host=None, port=None, database=None, username=None, password=None) -> None:
        # self.__conn = 'http://{}:{}/query?db={}'.format(host, port, database)
        try:
            from influxdb import InfluxDBClient
        except Exception as e:
            logger.error(MyException.ERROR_DATABASE_WORK)
            logger.error(e)
            return
        key = str(host) + str(port) + str(database) + str(username) + str(password)
        if not self.__conn_pool.__contains__(key):
            client = InfluxDBClient(host, port, username, password, database)
            self.__conn_pool[key] = client
            self._link = key
        self._conn = self.__conn_pool[self._link]

    def insert(self, measurement: str, tags: dict, fields: dict):
        try:
            json_body = [{"measurement": measurement, "tags": tags, "fields": fields}]
            result = self._conn.write_points(json_body)
            return result
        except Exception as e:
            logger.error(MyException.ERROR_DATABASE_EXEC)
            logger.error(json_body)
            logger.error(e)
            return None

    def execute(self, sql):
        try:
            result = self._conn.query(sql)
            return result
        except Exception as e:
            logger.error(MyException.ERROR_DATABASE_EXEC)
            logger.error(sql)
            logger.error(e)
            return None

    def select(self, sql):
        """
        查询多个结果
        :param sql: qsl语句
        :param param: sql参数
        :return: 结果数量和查询结果集
        """
        try:
            result = self._conn.query(sql)
            datas = list(result.get_points())
            return len(datas), datas
        except Exception as e:
            logger.error(MyException.ERROR_DATABASE_WORK)
            logger.error(e)
            return None
